let sql = '';
let total = 2;

for (let i = 1; i <= total; i++) {
    sql += `
SELECT 
    cr.openId,
    -- 优先使用升级表的最高等级，没有则用登录表的等级
    MAX(
        COALESCE(
            upgrade_max_level.max_castle_level,  -- 升级表最高等级
            eg.castleLevel,                      -- 登录表等级
            0                                    -- 都没有则为 0
        )
    ) AS castleLevel,
    
    MAX(DATE_FORMAT(cr.ts, '%Y-%m-%d %H:%i:%s')) AS create_ts,
    MAX(${i}) as serverId,

    COALESCE((
        SELECT SUM(
            (UNIX_TIMESTAMP(CONVERT_TZ(o.offlineTime, '+05:00', '+00:00')) - (o.logintime / 1000)) / 60
        )
        FROM {db}.OfflineDcLog_${i} o 
        WHERE o.openId = cr.openId 
          AND o.ts >= '{createStart_str}' 
          AND o.ts <= '{createEnd_str}'
    ), 0) AS online_time,

    MAX(eg.country) AS country,
    MAX(eg.roleId) AS roleId,
    MAX(eg.roleName) AS roleName

FROM 
    {db}.CreateRoleDcLog_${i} cr

    -- Step 1: 获取登录表中每个玩家最新的登录记录（含等级）
    LEFT JOIN (
        SELECT 
            openId,
            MAX(castleLevel) AS castleLevel,
MAX(country) AS country,
    MAX(roleId) AS roleId,
    MAX(roleName) AS roleName

        FROM 
            {db}.EnterGameDcLog_${i}
        WHERE ts >= '{enter_str}' AND ts <= '{enterEnd_str}'
        GROUP BY openId
    ) eg ON cr.openId = eg.openId 

    -- Step 2: 获取升级表中每个玩家达到的最高等级
    LEFT JOIN (
        SELECT 
            openId,
            MAX(level) AS max_castle_level
        FROM 
            {db}.InnerCityDcLog_${i}  -- 假设升级表是 InnerCityDcLog
        WHERE 
            ts >= '{createStart_str}' 
            AND ts <= '{createEnd_str}'
            AND protoType = 1 
        GROUP BY 
            openId
    ) upgrade_max_level ON cr.openId = upgrade_max_level.openId

WHERE 
    cr.ts >= '{createStart_str}' 
    AND cr.ts <= '{createEnd_str}'

GROUP BY 
    cr.openId
ORDER BY 
    online_time DESC
    
    limit 1000

`;

    if (i < total) {
        sql += ' UNION all '
    }
}

return sql;

const response = await apiExecuteTemplate1({
    sql: sql
});

return response;

